{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "be1bde41-91f1-40ef-91ab-f55874d0519d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "ERROR: Could not find a version that satisfies the requirement sqlite3 (from versions: none)\n",
      "ERROR: No matching distribution found for sqlite3\n"
     ]
    }
   ],
   "source": [
    "pip install sqlite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "c01124d2-66da-4cb3-94d9-4d4cc79385d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('./data/sales.db')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c1aabdb2-6bda-406d-af06-d9112b37310b",
   "metadata": {},
   "outputs": [],
   "source": [
    "cursor = conn.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "410111ef-89a8-47e6-bd01-24f38852e20e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x225bcc594c0>"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"\"\"\n",
    "    CREATE TABLE IF NOT EXISTS HOME_APPLIANCES(\n",
    "        BRAND  VARCHAR(50),\n",
    "        MODEL VARCHAR(100),\n",
    "        CATEGORY VARCHAR(20),\n",
    "        FEATURE VARCHAR(2000),\n",
    "        PRICE FLOAT,\n",
    "        IMAGE VARCHAR(500)\n",
    "    )\"\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "d4ea07bc-1d87-4290-a913-fbed31210623",
   "metadata": {
    "jupyter": {
     "source_hidden": true
    }
   },
   "outputs": [],
   "source": [
    "home_appliances = [\n",
    "    {\n",
    "        'brand': '小米',\n",
    "        'model': 'KFR-35GW/F5A1',\n",
    "        'category': '空调',\n",
    "        'feature': '新风空调Pro 超一级能效 变频冷暖 60m3/h大新风量 空调挂机 静音 省电',\n",
    "        'price': 3399.00,\n",
    "        'image': 'https://img13.360buyimg.com/n1/jfs/t1/172146/22/43567/101922/660a23b7F650f7fde/47cdeac7dfa2cff0.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '格力',\n",
    "        'model': 'KFR-35GW/NhGc1B',\n",
    "        'category': '空调',\n",
    "        'feature': '1.5匹 云佳 新一级能效 变频冷暖 自清洁 壁挂式空调挂机 静音 手机、小度控制 智能  ',\n",
    "        'price': 2848.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/180856/9/43850/152055/6618942eFeacf6a72/803efb3c6831b5cb.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '美的',\n",
    "        'model': 'KFR-26GW/N8KS1-3',\n",
    "        'category': '空调',\n",
    "        'feature': '空调 大1匹 酷省电 三级能效 变频冷暖 自清洁 壁挂式空调挂机 智能家电 静音 语音',\n",
    "        'price': 3399.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/242188/3/5669/221609/661afa3bF08d7093f/0ff68936f91b91cc.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '康佳',\n",
    "        'model': '65E8A',\n",
    "        'category': '电视',\n",
    "        'feature': '高刷护眼电视 4K超清全面屏投屏 智能语音液晶平板电视机以旧换新',\n",
    "        'price': 1999.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/186707/27/43632/121509/66190963F83ea62e2/51e3f5dfde1e5793.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '米家',\n",
    "        'model': 'A55',\n",
    "        'category': '电视',\n",
    "        'feature': '金属全面屏 双频WiFi 55英寸4K超高清液晶智能平板电视机L55MA-A',\n",
    "        'price': 1699.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/168223/12/43727/133819/661a0cd9Fe493d019/3ace1d30d005cdc5.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '华为',\n",
    "        'model': 'HD65KUNA',\n",
    "        'category': '电视',\n",
    "        'feature': '华为Vision智慧屏SE3 65英寸 超级投屏4K超高清120Hz全面屏 AI摄像头智能液晶超薄护眼电视机HD65KUNA',\n",
    "        'price': 3399.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/132022/15/43432/60381/66123657Fcfa177e8/346ee4034e107cf8.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '海尔',\n",
    "        'model': 'EG100MATE2S',\n",
    "        'category': '洗衣机',\n",
    "        'feature': '【1.08洗净比】滚筒洗衣机全自动10公斤大容量BLDC变频电机除菌除螨节能羽绒洗香薰筒自洁mate2s',\n",
    "        'price': 1799.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/234276/13/16025/195484/6619684eF88bcf6b5/707efc5a7febb794.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '小天鹅',\n",
    "        'model': 'TB100V23H',\n",
    "        'category': '洗衣机',\n",
    "        'feature': '波轮洗衣机全自动 10公斤大容量 升级鲸浪柔洗 免清洗不脏桶 除螨 以旧换新TB100V23H',\n",
    "        'price': 3399.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/240667/17/7226/188883/6619045fF5cc68adc/262e156fcc624a78.jpg.avif'\n",
    "    },\n",
    "    {\n",
    "        'brand': '卡萨帝',\n",
    "        'model': 'H10S3CU1',\n",
    "        'category': '洗衣机',\n",
    "        'feature': '滚筒洗衣机全自动 10公斤洗烘一体机 直驱变频防震动 奢护汽空气洗 紫外线除菌 晶彩欧卡 H10S3CU1',\n",
    "        'price': 6598.00,\n",
    "        'image': 'https://img14.360buyimg.com/n0/jfs/t1/230113/2/15412/101935/6618f80bF666f67a7/69c8dda1f1ac0f29.jpg.avif'\n",
    "    }    \n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "1081e47e-c4c1-4204-b260-7141e9eaa68f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x225bcc594c0>"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = []\n",
    "for i in home_appliances:\n",
    "    data.append((i['brand'], i['model'], i['category'], i['feature'], i['price'], i['image']))\n",
    "cursor.executemany('insert into HOME_APPLIANCES(BRAND, MODEL, CATEGORY, FEATURE, PRICE, IMAGE) values(?, ?, ?, ?, ?, ?)', data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0f274ccb-b7ee-4940-9a32-ee16d87ba47d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<sqlite3.Cursor object at 0x00000225BCC594C0>\n"
     ]
    }
   ],
   "source": [
    "result = cursor.execute(\"select * from HOME_APPLIANCES\")\n",
    "print(result)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "a292b2a0-292b-4c7f-99f1-fb98c62283aa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('小米', 'KFR-35GW/F5A1', '空调', '新风空调Pro 超一级能效 变频冷暖 60m3/h大新风量 空调挂机 静音 省电', 3399.0, 'https://img13.360buyimg.com/n1/jfs/t1/172146/22/43567/101922/660a23b7F650f7fde/47cdeac7dfa2cff0.jpg.avif'), ('格力', 'KFR-35GW/NhGc1B', '空调', '1.5匹 云佳 新一级能效 变频冷暖 自清洁 壁挂式空调挂机 静音 手机、小度控制 智能  ', 2848.0, 'https://img14.360buyimg.com/n0/jfs/t1/180856/9/43850/152055/6618942eFeacf6a72/803efb3c6831b5cb.jpg.avif'), ('美的', 'KFR-26GW/N8KS1-3', '空调', '空调 大1匹 酷省电 三级能效 变频冷暖 自清洁 壁挂式空调挂机 智能家电 静音 语音', 3399.0, 'https://img14.360buyimg.com/n0/jfs/t1/242188/3/5669/221609/661afa3bF08d7093f/0ff68936f91b91cc.jpg.avif'), ('康佳', '65E8A', '电视', '高刷护眼电视 4K超清全面屏投屏 智能语音液晶平板电视机以旧换新', 1999.0, 'https://img14.360buyimg.com/n0/jfs/t1/186707/27/43632/121509/66190963F83ea62e2/51e3f5dfde1e5793.jpg.avif'), ('米家', 'A55', '电视', '金属全面屏 双频WiFi 55英寸4K超高清液晶智能平板电视机L55MA-A', 1699.0, 'https://img14.360buyimg.com/n0/jfs/t1/168223/12/43727/133819/661a0cd9Fe493d019/3ace1d30d005cdc5.jpg.avif'), ('华为', 'HD65KUNA', '电视', '华为Vision智慧屏SE3 65英寸 超级投屏4K超高清120Hz全面屏 AI摄像头智能液晶超薄护眼电视机HD65KUNA', 3399.0, 'https://img14.360buyimg.com/n0/jfs/t1/132022/15/43432/60381/66123657Fcfa177e8/346ee4034e107cf8.jpg.avif'), ('海尔', 'EG100MATE2S', '洗衣机', '【1.08洗净比】滚筒洗衣机全自动10公斤大容量BLDC变频电机除菌除螨节能羽绒洗香薰筒自洁mate2s', 1799.0, 'https://img14.360buyimg.com/n0/jfs/t1/234276/13/16025/195484/6619684eF88bcf6b5/707efc5a7febb794.jpg.avif'), ('小天鹅', 'TB100V23H', '洗衣机', '波轮洗衣机全自动 10公斤大容量 升级鲸浪柔洗 免清洗不脏桶 除螨 以旧换新TB100V23H', 3399.0, 'https://img14.360buyimg.com/n0/jfs/t1/240667/17/7226/188883/6619045fF5cc68adc/262e156fcc624a78.jpg.avif'), ('卡萨帝', 'H10S3CU1', '洗衣机', '滚筒洗衣机全自动 10公斤洗烘一体机 直驱变频防震动 奢护汽空气洗 紫外线除菌 晶彩欧卡 H10S3CU1', 6598.0, 'https://img14.360buyimg.com/n0/jfs/t1/230113/2/15412/101935/6618f80bF666f67a7/69c8dda1f1ac0f29.jpg.avif')]\n"
     ]
    }
   ],
   "source": [
    "cursor.execute(\"select * from HOME_APPLIANCES\")\n",
    "rows = cursor.fetchall()\n",
    "print(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "b318a5e1-4038-4de3-87ec-487064279fbe",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<sqlite3.Cursor at 0x225bcc594c0>"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cursor.execute(\"select * from home_appliances where 1=1  and category like '%空调%' and feature like '%静音%' limit 3 offset 0\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "eea8c1ef-08e4-439e-9ffd-ae9c4a2a3b2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "rows = cursor.fetchall()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "297d88a9-2464-4a5b-b238-8de8efc59e57",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('小米', 'KFR-35GW/F5A1', '空调', '新风空调Pro 超一级能效 变频冷暖 60m3/h大新风量 空调挂机 静音 省电', 3399.0, 'https://img13.360buyimg.com/n1/jfs/t1/172146/22/43567/101922/660a23b7F650f7fde/47cdeac7dfa2cff0.jpg.avif'), ('格力', 'KFR-35GW/NhGc1B', '空调', '1.5匹 云佳 新一级能效 变频冷暖 自清洁 壁挂式空调挂机 静音 手机、小度控制 智能  ', 2848.0, 'https://img14.360buyimg.com/n0/jfs/t1/180856/9/43850/152055/6618942eFeacf6a72/803efb3c6831b5cb.jpg.avif'), ('美的', 'KFR-26GW/N8KS1-3', '空调', '空调 大1匹 酷省电 三级能效 变频冷暖 自清洁 壁挂式空调挂机 智能家电 静音 语音', 3399.0, 'https://img14.360buyimg.com/n0/jfs/t1/242188/3/5669/221609/661afa3bF08d7093f/0ff68936f91b91cc.jpg.avif')]\n"
     ]
    }
   ],
   "source": [
    "print(rows)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "b9fbcb10-0cab-44d7-bac7-19b688be17fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "conn.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b813267-1cb9-4537-8927-5ec94348a52e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
